IF OBJECT_ID('ssf.job_exec_get_status') IS NOT NULL
	DROP FUNCTION ssf.job_exec_get_status
GO

CREATE FUNCTION ssf.job_exec_get_status (
	@job_name NVARCHAR(128)
)
RETURNS XML
AS
BEGIN
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

DECLARE @job_status XML
;

;
WITH
ParentJob AS (
	SELECT j.parent_job_name
		, j.jobstep_id
		, j.jobstep_start_datetime
		, j.duration_ss
		, j.log_error_number
		, j.job_name
	FROM ssf.job_run_job_as_jobstep_log_info j
	WHERE j.parent_job_name = @job_name
)
SELECT @job_status = (
	SELECT @job_name AS "@JobName"
		, (
			SELECT jobstep_id AS "@StepID"
				, jobstep_start_datetime AS "StartTime"
				, duration_ss AS "DurationSeconds"
				, log_error_number AS "ErrorNumber"
				, (
					SELECT job_name AS "@JobName"
						, (
							SELECT step_id AS "@StepID"
								, step_name AS "StepName"
								, step_run_datetime AS "StartTime"
								, step_run_duration_ss AS "DurationSeconds"
								, CASE step_run_status
									WHEN 0 THEN N'Failed'
									WHEN 1 THEN N'Succeeded'
									WHEN 2 THEN N'Retry'
									WHEN 3 THEN N'Canceled'
									END AS "RunStatus"
							FROM ssf.job_get_run_step_status_info(
								ssf.job_get_job_id(p2.job_name)
								, ssf.job_get_first_run_datetime(ssf.job_get_job_id(p2.job_name), p2.jobstep_start_datetime)
							)
							ORDER BY step_id
							FOR XML PATH(N'JobStep'), TYPE
						)
					FROM ParentJob p2
					WHERE jobstep_id = p1.jobstep_id
					FOR XML PATH(N'JobStatus'), TYPE
				)
			FROM ParentJob p1
			ORDER BY jobstep_id
			FOR XML PATH(N'JobStep'), TYPE
		)
	FOR XML PATH(N'JobStatus'), TYPE
)
;

RETURN @job_status
;

END
GO
